const db = require('../../db/db.js')
const { getSqlData } = require('../../utils/sqlUtils.js')
const { checkIdExists } = require('../../utils/sqlUtils.js')

// 添加商品
exports.addGoods = (request, response) => {
  let data = request.body;
  let store_category_id = data?.store_category_id;
  let category_id = data?.category_id;
  let brand_id = data?.brand_id;
  // 检查store_category_id是否存在
  let checkCategorySql = 'SELECT id FROM store_category WHERE id = ?';
  let checkCategoryData = [data.store_category_id];

  db.query(checkCategorySql, checkCategoryData, (err, res) => {
    if (err) {
      return response.send(err)
    }
    if (res.length === 0) {
      store_category_id = null
    }

    // 如果存在，再检查其他外键
    let checkCategorySql = 'SELECT id FROM goods_category WHERE id = ?';
    let checkCategoryData = [data.category_id];
    db.query(checkCategorySql, checkCategoryData, (err, res) => {
      if (err) {
        return response.send(err)
      }
      if (res.length === 0) {
        category_id = null
      }

      // 继续检查 brand_id
      let checkBrandSql = 'SELECT id FROM goods_brand WHERE id = ?';
      let checkBrandData = [data.brand_id];
      db.query(checkBrandSql, checkBrandData, (err, res) => {
        if (err) {
          return response.send(err)
        }
        if (res.length === 0) {
          brand_id = null
        }

        // 所有外键检查通过后，插入商品
        let insertSql = 'INSERT INTO goods SET ?';
        let goodsData = { ...data, store_category_id: store_category_id, category_id: category_id, brand_id: brand_id }
        db.query(insertSql, goodsData, (err, result) => {
          if (err) {
            return response.status(500).send({ code: 500, message: '商品添加失败', error: err });
          }
          return response.status(200).send({ code: 200, message: '商品添加成功！', data: goodsData });
        });
      });
    });
  });
};

// 修改商品
exports.setGoods = async (request, response) => {
  let data = request.body;
  let goodsId = request.params?.id;

  if (!goodsId) {
    return response.status(400).send({
      code: 400,
      message: '商品ID不能为空。',
    });
  }

  try {
    // 检查store_category_id、category_id和brand_id是否存在
    const store_category_id = await checkIdExists(data?.store_category_id, 'store_category');
    const category_id = await checkIdExists(data?.category_id, 'goods_category');
    const brand_id = await checkIdExists(data?.brand_id, 'goods_brand');

    // 更新商品
    const updateSql = 'UPDATE goods SET ? WHERE id = ?';
    const goodsData = { ...data, store_category_id, category_id, brand_id };
    db.query(updateSql, [goodsData, goodsId], (err, result) => {
      if (err) {
        return response.status(500).send({ code: 500, message: '更新失败！', error: err });
      }
      if (result.affectedRows === 0) {
        return response.status(404).send({ code: 404, message: '未找到该商品' });
      }
      return response.status(200).send({ code: 200, message: '商品修改成功！', data: goodsData });
    });

  } catch (err) {
    return response.status(500).send({ code: 500, message: '服务器内部错误', error: err });
  }
};

// 获取商品
exports.getGoods = (request, response) => {
  let id = request.params?.id;
  let category_id = request.query?.category_id;
  let store_category_id = request.query?.store_category_id;
  let brand_id = request.query?.brand_id;
  let is_recommend = request.query?.is_recommend;
  let is_new = request.query?.is_new;
  let is_hot = request.query?.is_hot;
  let is_on_sale = request.query?.is_on_sale;
  let is_delete = request.query?.is_delete;
  let keyword = request.query?.keyword;

  // 分页参数
  let page = parseInt(request.query?.page) || 1; // 默认第1页
  let pageSize = parseInt(request.query?.page_size) || 20; // 默认每页20条记录

  let conditions = [];
  let queryParams = [];

  if (keyword) {
    conditions.push('goods_name LIKE ?');
    queryParams.push(`%${keyword}%`);
  }

  // 添加查询条件
  if (id) {
    conditions.push('id = ?');
    queryParams.push(id);
  }
  if (category_id) {
    conditions.push('category_id = ?');
    queryParams.push(category_id);
  }
  if (store_category_id) {
    conditions.push('store_category_id = ?');
    queryParams.push(store_category_id);
  }
  if (brand_id) {
    conditions.push('brand_id = ?');
    queryParams.push(brand_id);
  }
  if (is_recommend) {
    conditions.push('is_recommend = ?');
    queryParams.push(is_recommend);
  }
  if (is_new) {
    conditions.push('is_new = ?');
    queryParams.push(is_new);
  }
  if (is_hot) {
    conditions.push('is_hot = ?');
    queryParams.push(is_hot);
  }
  if (is_on_sale) {
    conditions.push('is_on_sale = ?');
    queryParams.push(is_on_sale);
  }
  if (is_delete) {
    conditions.push('is_delete = ?');
    queryParams.push(is_delete);
  }

  let whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
  let offset = (page - 1) * pageSize;

  // SQL 查询，包括分页
  let sql = `SELECT * FROM goods ${whereClause} LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 查询数据和总条数
  let countSql = `SELECT COUNT(*) AS total FROM goods ${whereClause}`;

  db.query(sql, queryParams, (err, results) => {
    if (err) {
      return response.send({
        code: 500,
        message: '查询失败！',
        error: err
      });
    }

    db.query(countSql, queryParams.slice(0, -2), (err, countResult) => {
      if (err) {
        return response.send({
          code: 500,
          message: '总条数查询失败！',
          error: err
        });
      }

      let total = countResult[0].total;
      let last_page = Math.ceil(total / pageSize);

      response.send({
        code: 200,
        message: '查询成功！',
        current_page: page,
        last_page: last_page,
        total: total,
        page_size: pageSize,
        data: results
      });
    });
  });
};

// 添加商品分类
exports.addGoodsCategory = (request, response) => {
  let data = request.body;

  // 检查是否已存在相同名称和父分类的记录
  let getSql = `SELECT * FROM goods_category WHERE name = ? AND parent_id = ?`;
  db.query(getSql, [data.name, data.parent_id], (err, res) => {
    if (err) {
      return response.send(err);
    }
    if (res.length > 0) {
      return response.send({
        code: 200,
        message: '该店铺分类已存在！',
        data: res[0]
      });
    } else {
      // 检查 parent_id 是否有效，若无效则设置为 NULL
      let validParentId = data.parent_id;
      if (data.parent_id) {
        // 检查 parent_id 是否存在于表中
        let checkParentSql = `SELECT id FROM goods_category WHERE id = ?`;
        db.query(checkParentSql, [data.parent_id], (err, result) => {
          if (err) {
            return response.send(err);
          }
          if (result.length === 0) {
            // 如果 parent_id 不存在，则设置为 NULL
            validParentId = null;
          }

          // 插入新分类
          let sql = `INSERT INTO goods_category SET ?`;
          let sqlData = { ...data, parent_id: validParentId };

          db.query(sql, sqlData, (err, result) => {
            if (err) {
              return response.send(err);
            }
            response.send({ code: 200, message: '店铺分类添加成功！', data: data, });
          });
        });
      } else {
        // 若没有提供 parent_id，则设置为 NULL
        let sql = `INSERT INTO goods_category SET ?`;
        let sqlData = { ...data, parent_id: null };

        db.query(sql, sqlData, (err, result) => {
          if (err) {
            return response.send(err);
          }
          response.send({ code: 200, message: '分类添加成功！', data: data, });
        });
      }
    }
  });
};

// 获取商品分类
exports.getGoodsCategory = (request, response) => {
  let id = request.params?.id;
  let parent_id = request.query?.parent_id;
  let keyword = request.query?.keyword;

  // 分页参数
  let page = parseInt(request.query?.page) || 1; // 默认第1页
  let pageSize = parseInt(request.query?.page_size) || 20; // 默认每页20条记录
  let offset = (page - 1) * pageSize;

  let conditions = [];
  let queryParams = [];

  if (id) {
    conditions.push('id = ?');
    queryParams.push(id);
  }
  if (parent_id) {
    conditions.push('parent_id = ?');
    queryParams.push(parent_id);
  }
  if (keyword) {
    conditions.push('name LIKE ?');
    queryParams.push(`%${keyword}%`);
  }

  let whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

  // 查询数据
  let sql = `SELECT * FROM goods_category ${whereClause} LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 查询总条数
  let countSql = `SELECT COUNT(*) AS total FROM goods_category ${whereClause}`;

  db.query(sql, queryParams, (err, results) => {
    if (err) {
      return response.send({
        code: 500,
        message: '查询失败！',
        error: err
      });
    }

    db.query(countSql, queryParams.slice(0, -2), (err, countResult) => {
      if (err) {
        return response.send({
          code: 500,
          message: '总条数查询失败！',
          error: err
        });
      }

      let total = countResult[0].total;
      let last_page = Math.ceil(total / pageSize);

      response.send({
        code: 200,
        message: '查询成功！',
        current_page: page,
        last_page: last_page,
        total: total,
        page_size: pageSize,
        data: results
      });
    });
  });
};

// 修改商品分类
exports.setGoodsCategory = async (request, response) => {
  const data = request.body;
  const categoryId = request.params?.id;

  if (!categoryId) {
    return response.send({
      code: 400,
      message: '分类ID不能为空。',
    });
  }

  if (!data.name || typeof data.name !== 'string') {
    return response.send({
      code: 400,
      message: '分类名称不能为空且必须是字符串。',
    });
  }

  try {
    // 检查 parent_id 是否有效
    const validParentId = await checkIdExists(data.parent_id, 'goods_category');

    // 更新商品分类
    const updateSql = `UPDATE goods_category SET ? WHERE id = ?`;
    const sqlData = { ...data, parent_id: validParentId };

    db.query(updateSql, [sqlData, categoryId], (err, result) => {
      if (err) {
        return response.send(err);
      }
      if (result.affectedRows === 0) {
        return response.send({
          code: 404,
          message: '未找到该商品分类。',
        });
      }
      response.send({ code: 200, message: '商品分类修改成功！', data: sqlData });
    });
  } catch (err) {
    response.send({ code: 500, message: '服务器内部错误', error: err });
  }
};

// 添加商品品牌
exports.addGoodsBrand = (request, response) => {
  let data = request.body;

  // 验证数据的完整性
  if (!data.name) {
    return response.send({
      code: 400,
      message: '品牌名称不能为空且必须是字符串。',
    });
  }

  let getSql = `SELECT * FROM goods_brand WHERE name = ?`;
  db.query(getSql, [data.name], (err, res) => {
    if (err) {
      return response.send(err);
    }
    if (res.length > 0) {
      return response.send({
        code: 404,
        message: '该品牌已存在！',
      });
    } else {
      let sql = `INSERT INTO goods_brand SET ?`;
      db.query(sql, data, (err, res) => {
        if (err) {
          return response.send(err)
        }
        return response.send({
          code: 200,
          message: '商品品牌添加成功！',
          data,
        })
      })
    }
  });
};

// 获取商品品牌
exports.getGoodsBrand = (request, response) => {
  let id = request.params?.id;
  let keyword = request.query?.keyword;

  // 分页参数
  let page = parseInt(request.query?.page) || 1; // 默认第1页
  let pageSize = parseInt(request.query?.page_size) || 20; // 默认每页20条记录
  let offset = (page - 1) * pageSize;

  let conditions = [];
  let queryParams = [];

  if (id) {
    conditions.push('id = ?');
    queryParams.push(id);
  }
  if (keyword) {
    conditions.push('name LIKE ?');
    queryParams.push(`%${keyword}%`);
  }

  let whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

  // 查询数据
  let sql = `SELECT * FROM goods_brand ${whereClause} LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 查询总条数
  let countSql = `SELECT COUNT(*) AS total FROM goods_brand ${whereClause}`;

  // 执行查询
  db.query(sql, queryParams, (err, results) => {
    if (err) {
      return response.send({
        code: 500,
        message: '查询失败！',
        error: err
      });
    }

    db.query(countSql, queryParams.slice(0, -2), (err, countResult) => {
      if (err) {
        return response.send({
          code: 500,
          message: '总条数查询失败！',
          error: err
        });
      }

      let total = countResult[0].total;
      let last_page = Math.ceil(total / pageSize);

      response.send({
        code: 200,
        message: '查询成功！',
        current_page: page,
        last_page: last_page,
        total: total,
        page_size: pageSize,
        data: results
      });
    });
  });
};

// 修改商品品牌
exports.setGoodsBrand = async (request, response) => {
  const data = request.body;
  const brandId = request.params?.id;

  if (!brandId) {
    return response.send({
      code: 400,
      message: '品牌ID不能为空。',
    });
  }

  if (!data.name || typeof data.name !== 'string') {
    return response.send({
      code: 400,
      message: '品牌名称不能为空且必须是字符串。',
    });
  }

  try {
    // 检查品牌是否存在
    const exists = await checkIdExists(brandId, 'goods_brand');
    if (!exists) {
      return response.send({
        code: 404,
        message: '该商品品牌不存在！',
      });
    }

    // 更新商品品牌
    const updateSql = `UPDATE goods_brand SET ? WHERE id = ?`;
    db.query(updateSql, [data, brandId], (err, res) => {
      if (err) {
        return response.send(err);
      }
      if (res.affectedRows === 0) {
        return response.send({
          code: 404,
          message: '未找到该商品品牌。',
        });
      }
      return response.send({
        code: 200,
        message: '商品品牌修改成功！',
        data,
      });
    });
  } catch (err) {
    response.send({ code: 500, message: '服务器内部错误', error: err });
  }
};

// 添加店铺商品分类
exports.addStoreGoodsCategory = (request, response) => {
  let data = request.body;

  // 先检查是否已有相同名称的分类存在
  let getSql = `SELECT * FROM store_category WHERE name = ? AND parent_id = ?`;
  db.query(getSql, [data.name, data.parent_id], (err, res) => {
    if (err) {
      return response.send(err);
    }
    if (res.length > 0) {
      return response.send({
        code: 404,
        message: '该店铺分类已存在！',
      });
    } else {
      // 检查 parent_id 是否有效，若无效则设置为 NULL
      let validParentId = data.parent_id;
      if (data.parent_id) {
        // 检查 parent_id 是否存在于表中
        let checkParentSql = `SELECT id FROM store_category WHERE id = ?`;
        db.query(checkParentSql, [data.parent_id], (err, result) => {
          if (err) {
            return response.send(err);
          }
          if (result.length === 0) {
            // 如果 parent_id 不存在，则设置为 NULL
            validParentId = null;
          }

          // 插入新分类
          let sql = `INSERT INTO store_category SET ?`;
          let sqlData = { ...data, parent_id: validParentId };

          db.query(sql, sqlData, (err, result) => {
            if (err) {
              return response.send(err);
            }
            response.send({ code: 200, message: '店铺分类添加成功！', data: data, });
          });
        });
      } else {
        // 若没有提供 parent_id，则设置为 NULL
        let sql = `INSERT INTO store_category SET ?`;
        let sqlData = { ...data, parent_id: null };

        db.query(sql, sqlData, (err, result) => {
          if (err) {
            return response.send(err);
          }
          response.send({ code: 200, message: '分类添加成功！', data: data, });
        });
      }
    }
  });
}

// 获取店铺商品分类
exports.getStoreGoodsCategory = (request, response) => {
  let id = request.params?.id;
  let parent_id = request.query?.parent_id;
  let keyword = request.query?.keyword;

  // 分页参数
  let page = parseInt(request.query?.page) || 1; // 默认第1页
  let pageSize = parseInt(request.query?.page_size) || 20; // 默认每页20条记录
  let offset = (page - 1) * pageSize;

  let conditions = [];
  let queryParams = [];

  if (id) {
    conditions.push('id = ?');
    queryParams.push(id);
  }
  if (parent_id) {
    conditions.push('parent_id = ?');
    queryParams.push(parent_id);
  }
  if (keyword) {
    conditions.push('name LIKE ?');
    queryParams.push(`%${keyword}%`);
  }

  let whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

  // 查询数据
  let sql = `SELECT * FROM store_category ${whereClause} LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 查询总条数
  let countSql = `SELECT COUNT(*) AS total FROM store_category ${whereClause}`;

  db.query(sql, queryParams, (err, results) => {
    if (err) {
      return response.send({
        code: 500,
        message: '查询失败！',
        error: err
      });
    }

    db.query(countSql, queryParams.slice(0, -2), (err, countResult) => {
      if (err) {
        return response.send({
          code: 500,
          message: '总条数查询失败！',
          error: err
        });
      }

      let total = countResult[0].total;
      let last_page = Math.ceil(total / pageSize);

      response.send({
        code: 200,
        message: '查询成功！',
        current_page: page,
        last_page: last_page,
        total: total,
        page_size: pageSize,
        data: results
      });
    });
  });
};

// 修改店铺分类
exports.setStoreGoodsCategory = async (request, response) => {
  const data = request.body;
  const storeCategoryId = request.params?.id;

  if (!storeCategoryId) {
    return response.send({
      code: 400,
      message: '店铺分类ID不能为空！',
    });
  }

  if (!data.name || typeof data.name !== 'string') {
    return response.send({
      code: 400,
      message: '分类名称不能为空且必须是字符串。',
    });
  }

  try {
    // 检查 parent_id 是否有效
    let validParentId = data.parent_id ? await checkIdExists(data.parent_id, 'store_category') : null;

    // 更新店铺分类
    const updateSql = `UPDATE store_category SET ? WHERE id=?`;
    const sqlData = { ...data, parent_id: validParentId };

    db.query(updateSql, [sqlData, storeCategoryId], (err, result) => {
      if (err) {
        return response.send(err);
      }
      if (result.affectedRows === 0) {
        return response.send({
          code: 404,
          message: '未找到该店铺分类。',
        });
      }
      response.send({ code: 200, message: '店铺分类修改成功！', data: sqlData });
    });
  } catch (err) {
    response.send({ code: 500, message: '服务器内部错误', error: err });
  }
};

